﻿using System;
using System.Collections;
using System.Data;
using System.Data.Entity;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading;
using System.Windows.Forms;
using Util;

namespace IterationLife.PlatformBackEnd.WindowsForms
{
    public partial class District : Form
    {
        public District()
        {
            InitializeComponent();
        }

        private delegate void SetPos(string vinfo, int ipos);

        private void SetTextMesssage(string vinfo, int ipos = 0)
        {
            if (this.InvokeRequired)
            {
                SetPos setpos = SetTextMesssage;
                this.Invoke(setpos, vinfo, ipos);
            }
            else
            {
                this.progressBar1.Value = Convert.ToInt32(ipos);
                this.textBox2.AppendText(vinfo + "\r\n");
            }
        }


        private void District_Load(object sender, EventArgs e)
        {
        }

        /// <summary>
        /// 选择文件
        /// </summary>
        private void button1_Click(object sender, EventArgs e)
        {
            OpenFileDialog fileDialog = new OpenFileDialog();
            fileDialog.Filter = "(*.xlsx)|*.xlsx";
            if (fileDialog.ShowDialog() == DialogResult.OK)
            {
                string extension = Path.GetExtension(fileDialog.FileName);
                string[] str = { ".xls", ".xlsx" };
                if (!((IList)str).Contains(extension))
                {
                    MessageBox.Show("请选择EXCEL文件！");
                }
                else
                {
                    button2.Enabled = true;
                    button3.Enabled = true;
                    textBox1.Text = fileDialog.FileName;
                    SetTextMesssage($"已选择文件。 文件地址{fileDialog.FileName}。");
                }
            }
        }

        /// <summary>
        /// 插入数据
        /// </summary>
        private void button2_Click(object sender, EventArgs e)
        {
            Thread fThread = new Thread(InsertData);
            fThread.Start();
        }

        /// <summary>
        /// 覆盖数据
        /// </summary>
        private void button3_Click(object sender, EventArgs e)
        {
            SetTextMesssage("开始删除数据......");
            using (DistrictEntities context = new DistrictEntities())
            {
                context.Database.ExecuteSqlCommand("DELETE FROM [System].[Districts]");
                context.SaveChanges();
            }
            SetTextMesssage("数据删除成功");
            Thread fThread = new Thread(InsertData);
            fThread.Start();
        }

        private void progressBar1_Click(object sender, EventArgs e)
        {

        }

        private void InsertData()
        {
            SetTextMesssage("开始导入数据......");
            var dataTable = ReadExcelToTable(textBox1.Text);
            var count = 1;
            using (DistrictEntities context = new DistrictEntities())
            {
                foreach (DataRow item in dataTable.Rows)
                {
                    var id = Guid.NewGuid();
                    var district = new Districts
                    {
                        DistrictId = id,
                        Code = item["Id"].SafeString(),
                        ParentCode = item["ParentId"].SafeString() == "CN" ? string.Empty : item["ParentId"].SafeString(),
                        Name = item["Name"].SafeString(),
                        MergerName = item["MergerName"].SafeString(),
                        ShortName = item["ShortName"].SafeString(),
                        MergerShortName = item["MergerShortName"].SafeString(),
                        TelCode = item["TelCode"].SafeString(),
                        ZipCode = item["ZipCode"].SafeString(),
                        PinYin = GetPinYin(item["Pinyin"].SafeString()),
                        FullPinYin = item["Pinyin"].SafeString(),
                        English = item["English"].SafeString(),
                        Longitude = Util.Helpers.Convert.ToDecimal(item["Longitude"].SafeString(), 6),
                        Latitude = Util.Helpers.Convert.ToDecimal(item["Latitude"].SafeString(), 6),
                        Level = Util.Helpers.Enum.GetValue<DistrictLevel>(item["Level"].SafeString()),
                        Enabled = true,
                        Path = $"{id},",
                        CodePath = $"{item["Id"].SafeString()},",
                        SortId = 1,
                        Version = Encoding.Default.GetBytes(id.ToString().Substring(0, 7))
                    };
                    SetEntity(context, district);
                    AddData(context, district);
                    SetTextMesssage($"{count}/{dataTable.Rows.Count}:{district.Code} {district.Name}", 100 * count / dataTable.Rows.Count);
                    count++;
                }
                context.SaveChanges();
            }
            SetTextMesssage($"行政地区导入完成，共{count}条数据");
        }

        /// <summary>
        /// 添加数据
        /// </summary>
        private void AddData(DistrictEntities context, Districts entity)
        {
            context.Districts.Add(entity);
            context.SaveChanges();
        }

        private void SetEntity(DistrictEntities context, Districts entity)
        {
            if (entity.ParentCode.IsEmpty())
                return;
            var parentEntity = context.Districts.FirstOrDefault(u => u.Code == entity.ParentCode);
            if (parentEntity == null)
                return;
            entity.ParentId = parentEntity.DistrictId;
            entity.Path = parentEntity.Path + entity.Path;
            entity.CodePath = parentEntity.CodePath + entity.CodePath;
            entity.SortId = parentEntity.SortId + entity.SortId;
        }

        private string GetPinYin(string pinyin)
        {
            var cache = pinyin.Split(',');
            return cache.Aggregate("", (current, item) => current + item.Substring(0, 1));
        }

        ///<summary>
        ///读取xls\xlsx格式的Excel文件的方法 
        ///</ummary>
        ///<param name="path">待读取Excel的全路径</param>
        ///<returns></returns>
        private DataTable ReadExcelToTable(string path)
        {
            //连接字符串
            string connstring = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=yes;IMEX=1';";  // Office 07及以上版本不能出现多余的空格 而且分号注意
            //string connstring = Provider=Microsoft.JET.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=NO;IMEX=1';";  
            //Office 07以下版本 因为本人用Office2010 所以没有用到这个连接字符串  可根据自己的情况选择 或者程序判断要用哪一//个连接字符串

            using (OleDbConnection conn = new OleDbConnection(connstring))
            {
                conn.Open();
                DataTable sheetsName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });  //得到所有sheet的名字    
                string firstSheetName = sheetsName.Rows[0][2].ToString();   //得到第一个sheet的名字    
                string sql = string.Format("SELECT * FROM [{0}]", firstSheetName);  //查询字符串     
                OleDbDataAdapter ada = new OleDbDataAdapter(sql, connstring);
                DataSet set = new DataSet();
                ada.Fill(set);
                return set.Tables[0];
            }
        }

    }

    public class ValueEventArgs
        : EventArgs
    {
        public int Value { set; get; }
    }

}
